﻿Imports TBCC.UtilityMobile
Imports MySql.Data.MySqlClient
Imports System.Runtime.InteropServices

''' <summary>
''' 提供数据导出到Excel
''' </summary>
''' <remarks></remarks>
Public Class DataExport

    ''' <summary>
    ''' 导出车载数据到Excel中
    ''' </summary>
    ''' <param name="projectId">工程编号</param>
    ''' <param name="carNumber">车牌号</param>
    ''' <param name="dtBegin">开始时间</param>
    ''' <param name="dtEnd">结束时间</param>
    ''' <param name="data">车载数据</param>
    ''' <returns>返回Excel文件名称</returns>
    ''' <remarks></remarks>
    Public Shared Function Export(ByVal projectId As String, ByVal carNumber As String, ByVal dtBegin As DateTime, ByVal dtEnd As DateTime, ByVal data As DataSet) As String
        Try

            '' 检测数据是否为空
            If data Is Nothing Then
                Return ""
            ElseIf data.Tables.Count = 0 Then
                Return ""
            ElseIf data.Tables(0).Rows.Count = 0 Then
                Return "NULL"
            End If

            '' ============================================
            '' 创建目录和移除已经存在的文件
            Dim filepath As String = Application.StartupPath + "\temp\" + projectId + "\"
            Dim fileName As String = filepath + dtBegin.ToString("yyyyMMddHHmmss") + "_" + dtEnd.ToString("yyyyMMddHHmmss") + ".xls"
            Try

                If Not System.IO.Directory.Exists(filepath) Then
                    System.IO.Directory.CreateDirectory(filepath)
                End If
                If (System.IO.File.Exists(fileName)) Then

                    System.IO.File.Delete(fileName)
                End If
            Catch ex As Exception

            End Try

            '' =========================================
            '' Excel 操作
            Dim oxl As Excel.Application = Nothing
            Dim Missing As Object = System.Type.Missing

            Dim oWB As Excel.Workbook = Nothing
            Dim oSheet As Excel.Worksheet = Nothing
            Dim range As Excel.Range = Nothing
            Try

                oxl = New Excel.Application
                oxl.Visible = False
                oWB = oxl.Workbooks.Add(Missing)
                oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)


                Dim irow As Integer = 0
                Dim ht1, ht2, ht3, hrh1 As Decimal
                Dim lt1, lt2, lt3, lrh1 As Decimal
                Dim st1, st2, st3, srh1 As Decimal

                Dim bHasHumidity = False

                oSheet.Name = carNumber
                ''==========================================
                '' HEAD
                '' 时间	T1	T2	T3	RH1	最小温度	最大温度	平均温度	最小湿度	最大湿度	平均湿度	报警状态
                oSheet.Cells(1, 1) = "开始时间"
                oSheet.Cells(1, 2) = dtBegin.ToString()
                oSheet.Cells(1, 3) = "结束时间"
                oSheet.Cells(1, 4) = dtEnd.ToString()
                ''===
                oSheet.Cells(2, 1) = "时间"
                oSheet.Cells(2, 2) = "T1"
                oSheet.Cells(2, 3) = "T2"
                oSheet.Cells(2, 4) = "T3"
                oSheet.Cells(2, 5) = "RH1"
                oSheet.Cells(2, 6) = "最小温度"
                oSheet.Cells(2, 7) = "最大温度"
                oSheet.Cells(2, 8) = "平均温度"
                oSheet.Cells(2, 9) = "最小湿度"
                oSheet.Cells(2, 10) = "最大湿度"
                oSheet.Cells(2, 11) = "平均湿度"
                oSheet.Cells(2, 12) = "报警状态"
                ''===========================================
                ht1 = -300 : lt1 = 300
                ht2 = -300 : lt2 = 300
                ht3 = -300 : lt3 = 300
                hrh1 = -300 : lrh1 = 300
                st1 = 0 : st2 = 0 : st3 = 0 : srh1 = 0
                irow = 2

                Dim t1, t2, t3, rh1 As Decimal
                Dim dt As DateTime
                t1 = 0 : t2 = 0 : t3 = 0 : rh1 = 0
                Dim ialarm As Integer = 0
                Dim ivail As Integer = 0

                For Each r As DataRow In data.Tables(0).Rows
                    irow += 1

                    '' =======================================
                    '' UpdateTime,AI1,AI2.AI3,AI4,AlarmStatus
                    dt = Convert.ToDateTime(r("UpdateTime").ToString())
                    t1 = Convert.ToDecimal(r("AI1").ToString())
                    t2 = Convert.ToDecimal(r("AI2").ToString())
                    t3 = Convert.ToDecimal(r("AI3").ToString())
                    rh1 = Convert.ToDecimal(r("AI4").ToString())
                    ialarm = Convert.ToInt32(r("AlarmStatus").ToString())

                    bHasHumidity = rh1 > -298

                    oSheet.Cells(irow, 1) = dt.ToString("yyyy-MM-dd HH:mm:ss")

                    If (t1 < -298 Or t2 < -298 Or t3 < -298) Then
                        ivail += 1
                        oSheet.Cells(irow, 3) = "--"
                        oSheet.Cells(irow, 4) = "--"
                        oSheet.Cells(irow, 6) = "--"
                        oSheet.Cells(irow, 7) = "--"
                        oSheet.Cells(irow, 8) = "--"
                    Else
                        oSheet.Cells(irow, 2) = t1
                        oSheet.Cells(irow, 3) = t2
                        oSheet.Cells(irow, 4) = t3
                        oSheet.Cells(irow, 6) = Math.Min(t1, Math.Min(t2, t3))
                        oSheet.Cells(irow, 7) = Math.Max(t1, Math.Max(t2, t3))
                        oSheet.Cells(irow, 8) = ((t1 + t2 + t3) / 3).ToString("###.##")
                        If (ht1 < t1) Then ht1 = t1
                        If (ht2 < t2) Then ht2 = t2
                        If (ht3 < t3) Then ht3 = t3
                        If (lt1 > t1) Then lt1 = t1
                        If (lt2 > t2) Then lt2 = t2
                        If (lt3 > t3) Then lt3 = t3

                        st1 += t1
                        st2 += t2
                        st3 += t3
                    End If


                    If (bHasHumidity) Then
                        If (hrh1 < rh1) Then hrh1 = rh1
                        If (lrh1 > rh1) Then lrh1 = rh1
                        srh1 += rh1

                        oSheet.Cells(irow, 5) = rh1
                        oSheet.Cells(irow, 9) = rh1
                        oSheet.Cells(irow, 10) = rh1
                        oSheet.Cells(irow, 11) = rh1
                    Else
                        oSheet.Cells(irow, 5) = "--"
                        oSheet.Cells(irow, 9) = "--"
                        oSheet.Cells(irow, 10) = "--"
                        oSheet.Cells(irow, 11) = "--"
                    End If

                    '' 车载报警状态(1表示报警;2表示正常)
                    If ialarm = 1 Then
                        oSheet.Cells(irow, 12) = "报警"
                        ''  oSheet.Range(irow, 12).Style.Font.Color = System.Drawing.Color.Red
                        ''  oSheet.Range(irow, 12).Font.TintAndShade = 0
                    Else
                        oSheet.Cells(irow, 12) = "正常"
                    End If
                Next

                '' style
                range = oSheet.Range("A2", "L" & irow)
                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
                range.Borders.Weight = Excel.XlBorderWeight.xlThin

                '' ==========================================
                '' 统计 Header
                irow += 2
                oSheet.Cells(irow, 1) = "统计"
                oSheet.Cells(irow, 2) = "T1"
                oSheet.Cells(irow, 3) = "T2"
                oSheet.Cells(irow, 4) = "T3"
                oSheet.Cells(irow, 5) = "RH1"
                oSheet.Cells(irow + 1, 1) = "最大"
                oSheet.Cells(irow + 2, 1) = "最小"
                oSheet.Cells(irow + 3, 1) = "平均"

                '' ==============================================
                '' 统计数据
                oSheet.Cells(irow + 1, 2) = ht1
                oSheet.Cells(irow + 1, 3) = ht2
                oSheet.Cells(irow + 1, 4) = ht3
                If (bHasHumidity) Then
                    oSheet.Cells(irow + 1, 5) = hrh1
                Else
                    oSheet.Cells(irow + 1, 5) = "--"
                End If


                oSheet.Cells(irow + 2, 2) = lt1
                oSheet.Cells(irow + 2, 3) = lt2
                oSheet.Cells(irow + 2, 4) = lt3
                If (bHasHumidity) Then
                    oSheet.Cells(irow + 2, 5) = lrh1
                Else
                    oSheet.Cells(irow + 2, 5) = "--"
                End If

                If (irow - 4 - ivail >= 0) Then
                    oSheet.Cells(irow + 3, 2) = (st1 / (irow - 4 - ivail)).ToString("###.##")
                    oSheet.Cells(irow + 3, 3) = (st2 / (irow - 4 - ivail)).ToString("###.##")
                    oSheet.Cells(irow + 3, 4) = (st3 / (irow - 4 - ivail)).ToString("###.##")
                    If (bHasHumidity) Then
                        oSheet.Cells(irow + 3, 5) = (srh1 / (irow - 4 - ivail)).ToString("###.##")
                    Else
                        oSheet.Cells(irow + 3, 5) = "--"
                    End If
                End If


                oxl.Columns.AutoFit()
                '' style
                range = oSheet.Range("A" & irow, "E" & (irow + 3))
                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
                range.Borders.Weight = Excel.XlBorderWeight.xlThin
                ''===========================================
                '' clear
                oSheet.Protect("thermoberg", Missing, Missing, Missing, Missing, Missing, _
                                Missing, Missing, Missing, Missing, Missing, Missing, _
                                Missing, Missing, Missing, Missing)

                oxl.DisplayAlerts = False

                oWB.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, Missing, _
                    Missing, Missing, Missing, Excel.XlSaveAsAccessMode.xlNoChange, _
                    Missing, Missing, Missing, Missing, Missing)

                oxl.UserControl = True
                oWB.Close(False, Missing, Missing)
                oxl.Workbooks.Close()
                oxl.Quit()
            Catch ex As Exception

            Finally
                If (range IsNot Nothing) Then
                    Marshal.FinalReleaseComObject(range)
                    range = Nothing
                End If
                If (oSheet IsNot Nothing) Then
                    Marshal.FinalReleaseComObject(oSheet)
                    oSheet = Nothing
                End If

                If (oWB IsNot Nothing) Then
                    Marshal.FinalReleaseComObject(oWB)
                    oWB = Nothing
                End If

                If (oxl IsNot Nothing) Then
                    Marshal.FinalReleaseComObject(oxl)
                    oxl = Nothing
                End If
                GC.Collect()
                ''==========================
                '' Sleep 2s wait for excel colse
                System.Threading.Thread.Sleep(1000)
                GC.Collect()

            End Try

            Return fileName
        Catch ex As Exception
            Return ""
        End Try
    End Function

End Class
